package com.qdlc.p2p.dal.dao.impl;

import java.math.BigDecimal;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;

import javax.persistence.Query;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.google.common.base.Joiner;
import com.qdlc.p2p.common.constant.BorrowType;
import com.qdlc.p2p.common.constant.Global;
import com.qdlc.p2p.common.exception.BorrowException;
import com.qdlc.p2p.common.exception.BusinessException;
import com.qdlc.p2p.common.exception.UserException;
import com.qdlc.p2p.common.util.BeanUtil;
import com.qdlc.p2p.common.util.DateUtil;
import com.qdlc.p2p.common.util.NumberUtil;
import com.qdlc.p2p.common.util.Page;
import com.qdlc.p2p.common.util.StringUtil;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.common.model.jpa.PageDataList;
import com.qdlc.p2p.dal.common.model.jpa.QueryParam;
import com.qdlc.p2p.dal.common.model.jpa.SearchFilter.Operators;
import com.qdlc.p2p.dal.dao.BorrowDao;
import com.qdlc.p2p.dal.dao.VerifyLogDao;
import com.qdlc.p2p.dal.dto.Borrow;
import com.qdlc.p2p.dal.dto.User;
import com.qdlc.p2p.dal.dto.VerifyLog;
import com.qdlc.p2p.dal.model.BorrowModel;

@Repository("borrowDao")
public class BorrowDaoImpl extends BaseDaoImpl<Borrow> implements BorrowDao {

    private Logger logger = Logger.getLogger(BorrowDaoImpl.class);

    @Override
    public void modifyBorrowAndRepay(Borrow borrow) {
        StringBuffer sql = new StringBuffer(
                "UPDATE " + Global.DB_PREFIX + "borrow SET repayment_account = CAST(repayment_account AS DECIMAL(20,2))+ :repayment_account,");
        sql.append(" repayment_yes_account=CAST(repayment_yes_account AS DECIMAL(20,2))+ :repayment_yes_account ,");
        sql.append(" repayment_yes_interest=CAST(repayment_yes_interest AS DECIMAL(20,2))+ :repayment_yes_interest,");
        sql.append(" status = :status WHERE id = :id ");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("repayment_account", borrow.getRepaymentAccount());
        query.setParameter("repayment_yes_account", borrow.getRepaymentYesAccount());
        query.setParameter("repayment_yes_interest", borrow.getRepaymentYesInterest());
        query.setParameter("status", borrow.getStatus());
        query.setParameter("id", borrow.getId());
        int result = query.executeUpdate();
        if (result != 1) {
            throw new UserException("撤回标失败！", 1);
        }
    }

    @SuppressWarnings({"unchecked", "rawtypes"})
    @Override
    public List<BorrowModel> getIndexList(BorrowModel model) {
        List<Borrow> list;
        List params = new ArrayList();
        params.add(model.getType());
        params.add(" and borrow.type=?) order by add_time desc");
        list = getInviteList(model, params).getList();

        List<BorrowModel> modelList = new ArrayList<BorrowModel>();
        VerifyLogDao verifyLogDao = (VerifyLogDao) BeanUtil.getBean("verifyLogDao");
        for (int i = 0; i < list.size(); i++) {
            Borrow borrow = list.get(i);
            BorrowModel model_ = BorrowModel.instance(borrow);
            VerifyLog log = verifyLogDao.findByType(borrow.getId(), "borrow", 1);
            if (log != null) {
                model_.setStartTime(DateUtil.dateStr2(log.getTime()));
            }
            modelList.add(model_);
        }
        return modelList;
    }

    public int unfinshJinBorrowCount(long userId) {
        QueryParam param = QueryParam.getInstance();
        param.addParam("user.userId", userId);
        param.addOrFilter("status", 0, 1);
        param.addParam("type", BorrowType.TYPE_PROPERTY);
        return super.countByCriteria(param);
    }

    public double getRepayTotalWithJin(long userId) {
        String jpql = "SELECT SUM(repaymentAccount) FROM BorrowRepayment WHERE status = 0 AND user.userId = :userId AND borrow.type = "
                + BorrowType.TYPE_PROPERTY;
        Query query = em.createQuery(jpql);
        query.setParameter("userId", userId);
        Object obj = query.getSingleResult();
        if (obj == null) {
            obj = 0;
        }
        return Double.parseDouble(obj.toString());
    }

    @Override
    public Borrow tenderUpdate(long id, double account, double scales, int status, int tenderTimes) {
        String sql = "UPDATE Borrow SET tenderTimes = tenderTimes + 1, accountYes = round(accountYes + :account, 2), scales = :scales WHERE id = :id AND status = :status AND ROUND(accountYes + :account) <= account AND tenderTimes=:tenderTimes";

        Query query = em.createQuery(sql);
        query.setParameter("account", account);
        query.setParameter("scales", scales);
        query.setParameter("status", status);
        query.setParameter("tenderTimes", tenderTimes);
        query.setParameter("id", id);
        int count = query.executeUpdate();
        if (count < 1) {
            throw new BorrowException(MessageFormat.format("投标失败!此标可能已满!id:{0} 投标金额:{1}", id, account));
        }
        Borrow borrow = find(id);
        super.refresh(borrow);
        return borrow;
    }

    @Override
    public void updateFlowTotalYesCount(Borrow borrow) {
        // StringBuffer jpql = new
        // StringBuffer("UPDATE Borrow SET flowTotalYesCount = :flowTotalYesCount where id = :id AND type = ");
        // jpql.append(Constant.TYPE_FLOW);
        // if(borrow.getFlowTime()>0){
        // jpql.append(" and :flowTotalYesCount <= (flowTime*flowCount)");
        // }
        // Query query = em.createQuery(jpql.toString());
        // query.setParameter("flowTotalYesCount",
        // borrow.getFlowTotalYesCount());
        // query.setParameter("id", borrow.getId());
        // int count = query.executeUpdate();
        // if (count != 1) {
        // throw new BorrowException("投标失败！超过流转标可流转次数，还剩"
        // + ((borrow.getFlowTime() * borrow.getFlowCount()) -
        // borrow.getFlowTotalYesCount()) + "份可以投标！", 1);
        // }
    }

    @Override
    public void updateStatus(long id, int status, int preStatus) {
        String sql = "UPDATE Borrow SET status = :status WHERE id = :id AND status = :preStatus";
        Query query = em.createQuery(sql);
        query.setParameter("status", status);
        query.setParameter("id", id);
        query.setParameter("preStatus", preStatus);
        int result = query.executeUpdate();
        if (result != 1) {
            logger.error("标复审，更改标状态失败！复审失败！---------------- borrowId:" + id + ",result:" + result + ",status:" + status + ",preStatus:" + preStatus);
            throw new BusinessException("标复审，更改标状态失败！", 1);
        }
        refresh(super.find(id));
    }

    @Override
    public void updateStatus(long id, int status) {
        String sql = "UPDATE Borrow SET status = :status WHERE id = :id";
        Query query = em.createQuery(sql);
        query.setParameter("status", status);
        query.setParameter("id", id);
        int result = query.executeUpdate();
        if (result != 1) {
            throw new BusinessException("更改标状态失败！", 1);
        }
        refresh(super.find(id));
    }

    @Override
    public void updatefixedTime(long id, Date time) {
        String sql = "UPDATE Borrow SET fixedTime = :fixedTime WHERE id = :id";
        Query query = em.createQuery(sql);
        query.setParameter("fixedTime", time);
        query.setParameter("id", id);
        int result = query.executeUpdate();
        if (result != 1) {
            throw new BusinessException("更改标定时失败！", 1);
        }
        refresh(super.find(id));
    }

    @Override
    public void updateRegisterTime(long id, Date time) {
        String sql = "UPDATE Borrow SET registerTime = :registerTime WHERE id = :id";
        Query query = em.createQuery(sql);
        query.setParameter("registerTime", time);
        query.setParameter("id", id);
        int result = query.executeUpdate();
        if (result != 1) {
            throw new BusinessException("更改标登记失败！", 1);
        }
        refresh(super.find(id));
    }

    @Override
    public Borrow getBorrowByName(String name) {
        List<Borrow> list = super.findByProperty("name", name);
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public int trialCount(int status) {
        QueryParam param = QueryParam.getInstance().addParam("status", status);
        return countByCriteria(param);
    }

    @Override
    public int fullCount(int status) {

        QueryParam param = QueryParam.getInstance();
        param.addParam("status", 1);
        param.addParam("account", Operators.PROPERTY_EQ, "accountYes");

        return super.countByCriteria(param);
    }

    @Override
    public Object[] countByFinish() {
        String jpql = "select count(id),sum(account) from Borrow where status in (6,7,8)";
        Query query = em.createQuery(jpql);
        Object[] object = (Object[]) query.getSingleResult();
        return object;
    }

    @SuppressWarnings({"unchecked", "rawtypes"})
    @Override
    public PageDataList<Borrow> getInviteList(BorrowModel model, List params) {
        PageDataList<Borrow> pageDataList = new PageDataList<Borrow>();
        StringBuffer sb = new StringBuffer("select borrow.* from " + Global.DB_PREFIX + "borrow borrow," + Global.DB_PREFIX + "verify_log log "
                + "where (borrow.id=log.fid and log.type='borrow' "
                + "and borrow.status = 1 and borrow.scales!=100");
        sb.append(params.get(params.size() - 1));
        Query query = em.createNativeQuery(sb.toString(), Borrow.class);
        for (int i = 0; i < params.size() - 1; i++) {
            query.setParameter(i + 1, params.get(i));
        }
        Page page = new Page(query.getResultList().size(), model.getPage(), model.getRows());
        query.setFirstResult((model.getPage() - 1) * model.getRows());
        query.setMaxResults(model.getRows());
        List<Borrow> list = query.getResultList();
        pageDataList.setList(list);
        pageDataList.setPage(page);
        return pageDataList;
    }

    @Override
    public Borrow findNotFlow(long borrowId) {
        String sql = "select borrow.* from " + Global.DB_PREFIX + "borrow borrow "
                + "where (( borrow.account != borrow.account_yes) or (borrow.account = borrow.account_yes) or borrow.type =110) and borrow.id=?";
        Query query = em.createNativeQuery(sql, Borrow.class);
        query.setParameter(1, borrowId);
        return (Borrow) query.getSingleResult();
    }

    @Override
    public void modifyBidNo(long borrowId, String bidNo) {
        String sql = "UPDATE Borrow SET bid_no = :bidNo WHERE id = :borrowId ";
        Query query = em.createQuery(sql);
        query.setParameter("borrowId", borrowId);
        query.setParameter("bidNo", bidNo);
        int result = query.executeUpdate();
        if (result != 1) {
            throw new BusinessException("更新标号失败", 1);
        }
        refresh(super.find(borrowId));
    }

    @Override
    public void modifyGuaranteeNo(long borrowId, String guaranteeNo) {
        String sql = "UPDATE Borrow SET guarantee_no = :guaranteeNo WHERE id = :borrowId ";
        Query query = em.createQuery(sql);
        query.setParameter("borrowId", borrowId);
        query.setParameter("guaranteeNo", guaranteeNo);
        int result = query.executeUpdate();
        if (result != 1) {
            throw new BusinessException("更新担保号失败", 1);
        }
        refresh(super.find(borrowId));
    }

    @Override
    public int findByStatusAndUserId(long userId, int status1, int status2) {
        if (status2 != 0) {
            StringBuffer sql = new StringBuffer("SELECT COUNT(DISTINCT p1.id) FROM " + Global.DB_PREFIX + "borrow p1 WHERE p1.user_id = :userId AND ( p1.status = :statusA OR p1.status = :statusB )");
            Query query = em.createNativeQuery(sql.toString());
            query.setParameter("userId", userId);
            query.setParameter("statusA", status1);
            query.setParameter("statusB", status2);
            Object count = query.getSingleResult();
            if (count != null) {
                return Integer.parseInt(count.toString());
            }
        } else if (status1 == 1) {
            StringBuffer sql = new StringBuffer("SELECT COUNT(DISTINCT p1.id) as num FROM " + Global.DB_PREFIX + "borrow p1," + Global.DB_PREFIX + "user p2," + Global.DB_PREFIX + "verify_log p3 WHERE");
            sql.append(" p1.user_id = p2.user_id AND p1.user_id = :userId AND( p1.id=p3.fid AND p3.type='borrow'");
            sql.append(" AND p3.result = 1 AND p3.verify_type = 1 ");
            sql.append(" AND p1.status = :statusA AND p1.account_yes <= account OR p1.type =110 AND p1.status = :statusB )");
            Query query = em.createNativeQuery(sql.toString());
            query.setParameter("userId", userId);
            query.setParameter("statusA", status1);
            query.setParameter("statusB", status1);
            Object count = query.getSingleResult();
            if (count != null) {
                return Integer.parseInt(count.toString());
            }
        }
        return 0;
    }

    @Override
    public double findAccountTotalByStatus(long userId, int status1, int status2) {
        if (status2 != 0) {
            StringBuffer sql = new StringBuffer("SELECT SUM(p1.repayment_account - p1.repayment_yes_account) FROM " + Global.DB_PREFIX + "borrow p1 WHERE p1.user_id = :userId AND ( p1.status = :statusA OR p1.status = :statusB )");
            Query query = em.createNativeQuery(sql.toString());
            query.setParameter("userId", userId);
            query.setParameter("statusA", status1);
            query.setParameter("statusB", status2);
            Object count = query.getSingleResult();
            if (count != null) {
                return Double.parseDouble(count.toString());
            }

        } else if (status1 == 1) {// 正在借款金额
            StringBuffer sql = new StringBuffer("SELECT SUM(p1.account) FROM " + Global.DB_PREFIX + "borrow p1," + Global.DB_PREFIX + "user p2," + Global.DB_PREFIX + "verify_log p3 WHERE");
            sql.append(" p1.user_id = p2.user_id AND p1.user_id = :userId AND ( p1.id=p3.fid AND p3.type='borrow'");
            sql.append(" AND p3.result = 1 AND p3.verify_type = 1 ");
            sql.append(" AND p1.status = :status AND p1.account_yes <= account OR p1.type =110 AND p1.status = :status)");
            Query query = em.createNativeQuery(sql.toString());
            query.setParameter("userId", userId);
            query.setParameter("status", status1);
            Object count = query.getSingleResult();
            if (count != null) {
                return Double.parseDouble(count.toString());
            }
        }
        return 0;
    }

    @SuppressWarnings("unchecked")
    public List<Borrow> getInvestList(User user) {
        StringBuffer sql = new StringBuffer("SELECT p1.id,p1.uuid,p1.name,p1.account,p1.apr,p1.time_limit,IFNULL(p1.borrow_time_type,0),p1.put_start_time,p1.scales,p1.type FROM " + Global.DB_PREFIX + "borrow p1," + Global.DB_PREFIX + "user p2," + Global.DB_PREFIX + "verify_log p3 WHERE");
        sql.append(" p1.user_id = p2.user_id AND p1.user_id <> :userId AND");
        sql.append(" ( p1.id=p3.fid AND p3.type='borrow' AND p3.verify_type = 1 AND p3.result = 1 ");
        sql.append(" AND p1.status = 1  AND p1.account_yes < p1.account OR p1.type =110 AND p1.status = 1 AND p1.scales!=100");
        sql.append(" OR p1.status IN (3,6,7,8) ) GROUP BY p1.id ORDER BY p1.status ASC , p1.id DESC LIMIT 4");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("userId", user.getUserId());
        List<Object> list = query.getResultList();
        List<Borrow> list_ = new ArrayList<Borrow>();
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                Object[] obj = (Object[]) list.get(i);
                Borrow borrow = new Borrow();
                borrow.setId(Integer.parseInt(obj[0].toString()));
                borrow.setUuid(obj[1].toString());
                borrow.setName(obj[2].toString());
                borrow.setAccount(NumberUtil.getDouble2(obj[3].toString()));
                borrow.setApr(NumberUtil.getDouble2(obj[4].toString()));
                borrow.setTimeLimit(Integer.parseInt(obj[5].toString()));
                borrow.setBorrowTimeType(Integer.parseInt((obj[6].toString())));
                borrow.setPutStartTime((Date) obj[7]);
                borrow.setScales(NumberUtil.getDouble2(obj[8].toString()));
                borrow.setType(Integer.parseInt(obj[9].toString()));
                list_.add(borrow);
            }
        }
        return list_;
    }

    @SuppressWarnings("unchecked")
    @Override
    public PageDataList<Borrow> getList(BorrowModel model) {
        StringBuffer selectSql = new StringBuffer("SELECT p1.* FROM " + Global.DB_PREFIX + "borrow p1," + Global.DB_PREFIX + "user p2 WHERE p1.user_id = p2.user_id");
        StringBuffer sql = new StringBuffer();
        if (model != null) {
            long userId = 0;
            if (model.getUser() != null && model.getUser().getUserId() > 0) {
                sql.append(" AND p1.user_id = :userId");
                userId = model.getUser().getUserId();
            }
            int status = -2;
            if (model.getStatus() != 99) {
                switch (model.getStatus()) {
                    case -2://-2代表投资列表筛选条件是：状态（全部），在搜索全部的状态时候，尤其是状态等于1的时候，需要特殊处理，将过期的标剔除
                        sql.append(" AND (p1.status = 1  AND p1.account_yes <= p1.account");
                        sql.append(" OR p1.type =110 AND p1.status = 1 AND p1.scales!=100 OR p1.status IN (3,6,7,8))");
                        break;
                    case 1:
                        String scalesSql = "";
                        if (model.getScales() == 100) {
                            scalesSql = " AND p1.account_yes = account AND p1.scales = 100";
                        } else {
                            scalesSql = " AND p1.account_yes < account";
                        }
                        sql.append(" AND ( p1.status = 1" + scalesSql);
                        sql.append(" OR p1.type =110 AND p1.status = 1 " + scalesSql + ")");
                        break;
                    /*case 6:
                        sql.append(" AND p1.status in (3,6,7)");
                        break;*/
                    case 11:
                        sql.append(" AND p1.status in (0,11)");
                        break;
                    case 19:
                        sql.append(" AND p1.status = 1 AND p1.account_yes = account");
                        break;
                    case 49:
                        sql.append(" AND p1.status in (4,49)");
                        break;
                    case 59:
                        sql.append(" AND p1.status in (-2,5,59)");
                        break;
                    default:
                        status = model.getStatus();
                        sql.append(" AND p1.status = :status");
                        break;
                }
            }
            /*if (model.getScales() == 99) {
                sql.append(" AND p1.account_yes > account");
//              param.addParam("type", Operators.NOTEQ, 110); 删除对流转标的限制
            }*/
            String searchName = "";
            if (StringUtil.isNotBlank(model.getSearchName())) {
                searchName = model.getSearchName();
                sql.append(" AND (p1.name LIKE :searchName or p2.user_name LIKE :searchName or p2.real_name LIKE :searchName)");
            }
            String borrowName = "";
            if (StringUtil.isNotBlank(model.getBorrowName())) {
                borrowName = model.getBorrowName();
                sql.append(" AND p1.name LIKE :borrowName");
            }
            String userName = "";
            String realName = "";
            if (StringUtil.isNotBlank(model.getUserName())) {
                userName = model.getUserName();
                sql.append(" AND p2.user_name LIKE :userName");
            }
            if (StringUtil.isNotBlank(model.getRealName())) {
                realName = model.getRealName();
                sql.append(" AND p2.real_name LIKE :realName");
            }
            Date d = DateUtil.getDate(System.currentTimeMillis() / 1000 + "");
            Date startTime = null;
            Date endTime = null;
            if (model.getTime() == 7) {
                sql.append(" AND p1.add_time >= :startTime");
                sql.append(" AND p1.add_time <= :endTime");
                startTime = DateUtil.rollDay(d, -7);
                endTime = d;
            } else if (model.getTime() > 0 && model.getTime() < 4) {
                sql.append(" AND p1.add_time >= :startTime");
                sql.append(" AND p1.add_time <= :endTime");
                startTime = DateUtil.rollMon(d, -model.getTime());
                endTime = d;
            }
            if (StringUtil.isNotBlank(model.getStartTime())) {
                startTime = DateUtil.valueOf(model.getStartTime() + " 00:00:00");
                sql.append(" AND p1.add_time > :startTime");
            }
            if (StringUtil.isNotBlank(model.getEndTime())) {
                endTime = DateUtil.valueOf(model.getEndTime() + " 23:59:59");
                sql.append(" AND p1.add_time < :endTime");
            }
            int type = 0;
            if (model.getType() != 100 && model.getType() != 0) {
                type = model.getType();
                sql.append(" AND p1.type = :type");
            }

            if (model.getCategory() != null) {
                sql.append(" AND p1.category = :category");
            }else {
                sql.append(" AND p1.category != 2");
            }

            if (model.getCheck() == 1) {
                sql.append(" AND p1.is_recommend = 1");
            } else if (model.getCheck() == 2) {
                sql.append(" AND p1.is_recommend = 0");
            }

            sql.append(this.searchParam(model));
//            param.addPage(model.getPage(), model.getRows());
            PageDataList<Borrow> pageDataList = new PageDataList<Borrow>();
            selectSql.append(sql);
            Query query = em.createNativeQuery(selectSql.toString(), Borrow.class);
            if (userId > 0) {
                query.setParameter("userId", userId);
            }
            if (status > -2) {
                query.setParameter("status", status);
            }
            if (startTime != null) {
                query.setParameter("startTime", startTime);
            }
            if (endTime != null) {
                query.setParameter("endTime", endTime);
            }

            if (searchName.length() > 0) {
                query.setParameter("searchName", "%" + searchName.trim() + "%");
            }
            if (borrowName.length() > 0) {
                query.setParameter("borrowName", "%" + borrowName.trim() + "%");
            }
            if (userName.length() > 0) {
                query.setParameter("userName", "%" + userName.trim() + "%");
            }
            if (realName.length() > 0) {
                query.setParameter("realName", "%" + realName.trim() + "%");
            }
            if (type > 0) {
                query.setParameter("type", type);
            }
            if (model.getCategory() != null) {
                query.setParameter("category", model.getCategory());
            }
            Page page = new Page(query.getResultList().size(), model.getPage(), model.getRows());
            query.setFirstResult((model.getPage() - 1) * model.getRows());
            query.setMaxResults(model.getRows());
            List<Borrow> list = query.getResultList();
            pageDataList.setList(list);
            pageDataList.setPage(page);
            return pageDataList;
        }
        return null;
    }

    /**
     * 首页推荐标显示
     *
     * @param model 标model
     * @return
     */
    @Override
    public PageDataList<Borrow> getBorrowListForIndex(BorrowModel model) {
        StringBuilder selectSql = new StringBuilder("SELECT p.* FROM ( SELECT p1.* FROM " + Global.DB_PREFIX + "borrow p1," + Global.DB_PREFIX + "user p2 WHERE p1.user_id = p2.user_id");
        StringBuilder where = new StringBuilder();
        if (model != null) {
            where.append(" AND p1.status = 1  AND p1.account_yes < p1.account"); // 初审通过(status=1) 并且 实还总额少于总额 招标
            //where.append(")");
            // 标种 103:抵压标 112:担保标 102:信用标 101:秒还标
            if (model.getTypes() != null && model.getTypes().length != 0) {
                where.append(" AND p1.type in (" + Joiner.on(",").join(model.getTypes()) + ")");
            }

            // 标分类 1：普通标 2：新手标 3：体验标  【modify 0:新手标 1:普通标 2:体验标】
            if (model.getCategorys() != null && model.getCategorys().length != 0) {
                where.append(" AND p1.category in (" + Joiner.on(",").join(model.getCategorys()) + ")");
            }

            // 推荐标
            if (model.isRecommend()) {
                where.append(" AND p1.is_recommend = 1");
            // } else {
            //    where.append(" AND p1.is_recommend = 0");
            }

            // 是否推荐标 0：不推荐 1：推荐。这里固定了，如果没有推荐标则选出三个非推荐标
            selectSql.append(where);
            // selectSql.append(" ORDER BY p1.is_recommend desc, p1.add_time DESC LIMIT 0,3) p");
            selectSql.append(" ORDER BY p1.add_time DESC LIMIT 0,4) p");

            // >>两个非推荐标。如果没有推荐标，则选出三个非推荐标
            selectSql.append(" UNION ALL SELECT p.* FROM ( SELECT p1.* FROM " + Global.DB_PREFIX + "borrow p1," + Global.DB_PREFIX + "user p2 WHERE p1.user_id = p2.user_id");

            StringBuilder whereNotRe = new StringBuilder();
            whereNotRe.append(" AND p1.status in (1,3,7,6,8) AND p1.account_yes >= p1.account ");
            //whereNotRe.append(")"); // 3:复审通过、6:还款中、7:部分还款、8:还款成功

            // 标种 103:抵压标 112:担保标 102:信用标 101:秒还标
            if (model.getTypes() != null && model.getTypes().length != 0) {
                whereNotRe.append(" AND p1.type in (" + Joiner.on(",").join(model.getTypes()) + ")");
            }

            // 标分类 1：普通标 2：新手标 3：体验标
            if (model.getCategorys() != null && model.getCategorys().length != 0) {
                whereNotRe.append(" AND p1.category in (" + Joiner.on(",").join(model.getCategorys()) + ")");
            }
            // 推荐标
            if (model.isRecommend()) {
                whereNotRe.append(" AND p1.is_recommend = 1");
            // } else {
            //    where.append(" AND p1.is_recommend = 0");
            }
            PageDataList<Borrow> pageDataList = new PageDataList<Borrow>();
            selectSql.append(whereNotRe);
            // selectSql.append(" ORDER BY p1.is_recommend desc , p1.add_time DESC LIMIT 0,3) p");
            selectSql.append(" ORDER BY p1.add_time DESC LIMIT 0,4) p");
            Query query = em.createNativeQuery(selectSql.toString(), Borrow.class);

            // 分页查询。以上selectSql不用加[limit 0,3],Page会自动设置
            Page page = new Page(query.getResultList().size(), model.getPage(), model.getRows());
            query.setFirstResult((model.getPage() - 1) * model.getRows());
            query.setMaxResults(model.getRows());
            List<Borrow> list = query.getResultList();
            pageDataList.setList(list);
            pageDataList.setPage(page);
            return pageDataList;
        }
        return null;
    }

    /**
     * 首页推荐标显示
     *
     * @param model 标model
     * @return
     */
    @Override
    public PageDataList<Borrow> getBorrowListForIndexExp(BorrowModel model) {
        StringBuilder selectSql = new StringBuilder("SELECT p.* FROM ( SELECT p1.* FROM " + Global.DB_PREFIX + "borrow p1," + Global.DB_PREFIX + "user p2 WHERE p1.user_id = p2.user_id");
        StringBuilder where = new StringBuilder();
        if (model != null) {
            where.append(" AND p1.status = 1  AND p1.scales < 100"); // 初审通过(status=1) 并且 实还总额少于总额 招标
            // 已开标的体验标
            // 标种 103:抵压标 112:担保标 102:信用标 101:秒还标
            if (model.getTypes() != null && model.getTypes().length != 0) {
                where.append(" AND p1.type in (" + Joiner.on(",").join(model.getTypes()) + ")");
            }
            // 标分类 0:新手标 1:普通标 2:体验标
            if (model.getCategorys() != null && model.getCategorys().length != 0) {
                where.append(" AND p1.category in (" + Joiner.on(",").join(model.getCategorys()) + ")");
            }
            where.append(" AND UNIX_TIMESTAMP(NOW()) > UNIX_TIMESTAMP(p1.put_start_time) ");

            where.append(" AND (SELECT b.scales FROM " + Global.DB_PREFIX + "borrow b WHERE ");
            if (model.getCategorys() != null && model.getCategorys().length != 0) {
                where.append(" b.category in (" + Joiner.on(",").join(model.getCategorys()) + ")");
            }
            where.append(" AND UNIX_TIMESTAMP(NOW()) > UNIX_TIMESTAMP(b.put_start_time) ORDER BY b.add_time DESC LIMIT 1) != 100");

            selectSql.append(where);
            selectSql.append(" ORDER BY p1.add_time DESC LIMIT 1) p");

            // 未开标的体验标
            selectSql.append(" UNION ALL SELECT p.* FROM ( SELECT p1.* FROM " + Global.DB_PREFIX + "borrow p1," + Global.DB_PREFIX + "user p2 WHERE p1.user_id = p2.user_id");

            StringBuilder whereNotRe = new StringBuilder();
            whereNotRe.append(" AND p1.status = 1  AND p1.scales < 100 "); // 初审通过(status=1) 并且 实还总额少于总额 招标
            //whereNotRe.append(")"); // 3:复审通过、6:还款中、7:部分还款、8:还款成功

            // 标种 103:抵压标 112:担保标 102:信用标 101:秒还标
            if (model.getTypes() != null && model.getTypes().length != 0) {
                whereNotRe.append(" AND p1.type in (" + Joiner.on(",").join(model.getTypes()) + ")");
            }

            // 标分类 1：普通标 2：新手标 3：体验标
            if (model.getCategorys() != null && model.getCategorys().length != 0) {
                whereNotRe.append(" AND p1.category in (" + Joiner.on(",").join(model.getCategorys()) + ")");
            }
            whereNotRe.append(" AND UNIX_TIMESTAMP(NOW()) < UNIX_TIMESTAMP(p1.put_start_time) ");

            PageDataList<Borrow> pageDataList = new PageDataList<Borrow>();
            selectSql.append(whereNotRe);
            selectSql.append(" ORDER BY p1.add_time ASC LIMIT 1) p");
            Query query = em.createNativeQuery(selectSql.toString(), Borrow.class);

            // 分页查询。以上selectSql不用加[limit 0,3],Page会自动设置
            Page page = new Page(query.getResultList().size(), model.getPage(), model.getRows());
            query.setFirstResult((model.getPage() - 1) * model.getRows());
            query.setMaxResults(model.getRows());
            List<Borrow> list = query.getResultList();
            pageDataList.setList(list);
            pageDataList.setPage(page);
            return pageDataList;
        }
        return null;
    }

    private String searchParam(BorrowModel model) {
        // 利率
        StringBuffer sql = new StringBuffer("");
        if (model.getAprSearch() != -1) {
            switch (model.getAprSearch()) {
                case 1:
                    sql.append(" AND p1.apr >=8 AND p1.apr < 10");
                    break;
                case 2:
                    sql.append(" AND p1.apr >= 10 AND p1.apr < 12");
                    break;
                case 3:
                    sql.append(" AND p1.apr >= 12 AND p1.apr <= 15");
                    break;
                default:
                    break;
            }
        }
        // 金额
        if (model.getMoneySearch() != -1) {
            switch (model.getMoneySearch()) {
                case 2:
                    sql.append(" AND p1.account <= 10000");
                    break;
                case 3:
                    sql.append(" AND p1.account > 10000 AND p1.account <= 50000");
                    break;
                case 4:
                    sql.append(" AND p1.account > 50000 AND p1.account <= 100000");
                    break;
                case 5:
                    sql.append(" AND p1.account > 100000 ");
                    break;
                default:
                    break;
            }
        }
        // 期限
        if (model.getTimeSearch() != -1) {
            switch (model.getTimeSearch()) {
                case 2:
                    sql.append(" AND (p1.borrow_time_type = 1 OR p1.time_limit <= 1)");
                    break;
                case 3:
                    sql.append(" AND p1.borrow_time_type = 0 AND p1.time_limit > 1 AND p1.time_limit <= 3");
                    break;
                case 4:
                    sql.append(" AND p1.borrow_time_type = 0 AND p1.time_limit > 3 AND p1.time_limit <= 6");
                    break;
                default:
                    break;
            }
        }
        sql.append(" GROUP BY p1.id");

        //增加微信的排序方式 先按状态 再按是否是推荐标排序
        if(model.getOrder() == 5){
            sql.append(" ORDER BY p1.status ASC,(p1.account-p1.account_yes=0) ASC,p1.is_recommend DESC,p1.id DESC ");
            return sql.toString();
        }
        //推荐标置顶
        sql.append(" ORDER BY  ");
        // 排序 1金额 2利率 3进度 4信用
        switch (model.getOrder()) {
            case 0:
                sql.append("p1.status ASC ,p1.id DESC");
                break;
            case -1:
                sql.append("p1.is_recommend DESC ,p1.account ASC");
                break;
            case 1:
                sql.append("p1.is_recommend DESC ,p1.account DESC");
                break;
            case -2:
                sql.append("p1.is_recommend DESC ,p1.apr ASC");
                break;
            case 2:
                sql.append("p1.is_recommend DESC ,p1.apr DESC");
                break;
            case -3:
                sql.append("p1.is_recommend DESC ,p1.scales ASC");
                break;
            case 3:
                sql.append("p1.is_recommend DESC ,p1.scales DESC");
                break;
            case -4:
                break;
            case 4:
                break;
            default:
                sql.append("p1.is_recommend DESC, p1.id DESC");
                break;
        }
        return sql.toString();
    }

    @Override
    public double getBorrowAccountByDate(String date) {
        String dateFormat = "%Y-%m-%d";
        if (date.length() == 7) {
            dateFormat = "%Y-%m";
        }
        String sql = "select sum(account) from " + Global.DB_PREFIX + "borrow where date_format(add_time,'" + dateFormat + "') = ? AND  " + Global.DB_PREFIX + "borrow.category!=2 AND  " + Global.DB_PREFIX + "borrow.status IN(1,3,6,7,8)";
        Query query = em.createNativeQuery(sql).setParameter(1, date);
        Object obj = query.getSingleResult();
        double count = 0;
        if (obj != null) {
            count = ((BigDecimal) obj).doubleValue();
        }
        return count;
    }

    @Override
    public Borrow getBorrowByBidNo(String bidNo) {
        List<Borrow> list = super.findByProperty("bidNo", bidNo);
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public int count() {
        String sql = "SELECT COUNT(p1.id) FROM " + Global.DB_PREFIX + "borrow p1 WHERE (p1.type <> 110 AND p1.status IN (3,6,7,8)) OR (p1.type = 110 AND p1.status IN (1,8))";
        Query query = em.createNativeQuery(sql);
        Object count = query.getSingleResult();
        if (count != null) {
            return Integer.parseInt(count.toString());
        }
        return 0;
    }

    @Override
    public int getGuaranteeingCount(long userId) {
        String sql = "SELECT count(t.id) FROM " + Global.DB_PREFIX + "borrow t WHERE t.vouch_id=:vouch_id AND t.status IN ('1','3','6','7') AND LENGTH(t.guarantee_no) >0 AND t.guarantee_rate > 0";
        Query query = em.createNativeQuery(sql);
        query.setParameter("vouch_id", userId);
        Object count = query.getSingleResult();
        if (count != null) {
            return Integer.parseInt(count.toString());
        }
        return 0;
    }

    @Override
    public double getGuaranteeingAccount(long userId) {
        String sql = "SELECT SUM(t.account) FROM " + Global.DB_PREFIX + "borrow t WHERE t.vouch_id=:vouch_id AND t.status IN ('1','3','6','7') AND LENGTH(t.guarantee_no) >0 AND t.guarantee_rate > 0";
        Query query = em.createNativeQuery(sql);
        query.setParameter("vouch_id", userId);
        Object count = query.getSingleResult();
        if (count != null) {
            return Double.parseDouble(count.toString());
        }
        return 0;
    }

    @Override
    public int getNeedGuaranteeRegisteCount(long userId) {
        String sql = "SELECT count(t.id) FROM " + Global.DB_PREFIX + "borrow t WHERE t.vouch_id=:vouch_id AND t.status ='11' AND (t.guarantee_no is null OR LENGTH(t.guarantee_no) = 0)  AND t.guarantee_rate > 0";
        Query query = em.createNativeQuery(sql);
        query.setParameter("vouch_id", userId);
        Object count = query.getSingleResult();
        if (count != null) {
            return Integer.parseInt(count.toString());
        }
        return 0;
    }

    @Override
    public double getNeedGuaranteeRegisteAccount(long userId) {
        String sql = "SELECT SUM(t.account) FROM " + Global.DB_PREFIX + "borrow t WHERE t.vouch_id=:vouch_id AND t.status ='11' AND (t.guarantee_no is null OR LENGTH(t.guarantee_no) = 0) AND t.guarantee_rate > 0";
        Query query = em.createNativeQuery(sql);
        query.setParameter("vouch_id", userId);
        Object count = query.getSingleResult();
        if (count != null) {
            return Double.parseDouble(count.toString());
        }
        return 0;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Borrow> getNeedGuaranteeRegisteList(long userId) {
        String sql = "SELECT t.* FROM " + Global.DB_PREFIX + "borrow t WHERE t.vouch_id=:vouch_id AND t.status ='11' AND (t.guarantee_no is null OR LENGTH(t.guarantee_no) = 0) AND t.guarantee_rate > 0";
        Query query = em.createNativeQuery(sql, Borrow.class);
        query.setParameter("vouch_id", userId);
        List<Borrow> list = query.getResultList();
        return list;
    }

    @Override
    public int getAllOverduedCount() {
        String sql = "SELECT COUNT(DISTINCT b.id) FROM " + Global.DB_PREFIX + "borrow b, " + Global.DB_PREFIX + "borrow_repayment r WHERE b.id = r.borrow_id AND b.status = 8 AND r.repayment_time < r.repayment_yes_time";
        Query query = em.createNativeQuery(sql);
        Object count = query.getSingleResult();
        if (count != null) {
            return Integer.parseInt(count.toString());
        }
        return 0;
    }

    @Override
    public int getAllOverdueingCount() {
        String sql = "SELECT COUNT(DISTINCT b.id) FROM " + Global.DB_PREFIX + "borrow b, " + Global.DB_PREFIX + "borrow_repayment r WHERE b.id = r.borrow_id AND b.status in(6, 7)  AND r.status = 0 AND r.repayment_time < NOW()";
        Query query = em.createNativeQuery(sql);
        Object count = query.getSingleResult();
        if (count != null) {
            return Integer.parseInt(count.toString());
        }
        return 0;
    }

    @Override
    public double getAllOverdueingMomeny() {
        String sql = "SELECT SUM(b.account) FROM " + Global.DB_PREFIX + "borrow b WHERE b.status in(6, 7)  AND b.id IN(SELECT DISTINCT r.borrow_id FROM " + Global.DB_PREFIX + "borrow_repayment r WHERE r.status = 0 AND r.repayment_time < NOW())";
        Query query = em.createNativeQuery(sql);
        Object obj = query.getSingleResult();
        if (obj != null) {
            return Double.parseDouble(obj.toString());
        }
        return 0;
    }

    @Override
    public double getAllMomeny() {
        String sql = "SELECT SUM(b.account) FROM " + Global.DB_PREFIX + "borrow b where b.category!=2";
        Query query = em.createNativeQuery(sql);
        Object obj = query.getSingleResult();
        if (obj != null) {
            return Double.parseDouble(obj.toString());
        }
        return 0;
    }

    @Override
    public double getAllOverduedMomeny() {
        String sql = "SELECT SUM(b.account) FROM " + Global.DB_PREFIX + "borrow b WHERE b.status =8 AND b.id IN(SELECT DISTINCT r.borrow_id FROM " + Global.DB_PREFIX + "borrow_repayment r WHERE r.repayment_time < r.repayment_yes_time)";
        Query query = em.createNativeQuery(sql);
        Object obj = query.getSingleResult();
        if (obj != null) {
            return Double.parseDouble(obj.toString());
        }
        return 0;
    }

    @Override
    public double getMomenyByStatus(int status) {
        String sql = "SELECT SUM(b.account) FROM " + Global.DB_PREFIX + "borrow b WHERE b.status = :status and b.category!=2";
        Query query = em.createNativeQuery(sql);
        query.setParameter("status", status);
        Object obj = query.getSingleResult();
        if (obj != null) {
            return Double.parseDouble(obj.toString());
        }
        return 0;
    }

    @Override
    public double getVerifyFullMomeny() {
        String sql = "SELECT SUM(b.account) FROM " + Global.DB_PREFIX + "borrow b WHERE b.status = 1 AND b.scales = 100 and b.category!=2";
        Query query = em.createNativeQuery(sql);
        Object obj = query.getSingleResult();
        if (obj != null) {
            return Double.parseDouble(obj.toString());
        }
        return 0;
    }

    @SuppressWarnings("unchecked")
    @Override
    public PageDataList<Borrow> getConfirmedBorrowList(BorrowModel model) {

        StringBuffer selectSql = new StringBuffer("SELECT p1.* FROM " + Global.DB_PREFIX + "borrow p1, " + Global.DB_PREFIX + "user p2 WHERE p1.user_id = p2.user_id AND p1.status = 11 AND ((LENGTH(p1.guarantee_no) >0 AND p1.guarantee_rate > 0) OR p1.guarantee_rate = 0) ");
        StringBuffer sql = new StringBuffer();
        if (model != null) {
            String searchName = "";
            if (StringUtil.isNotBlank(model.getSearchName())) {
                searchName = model.getSearchName();
                sql.append(" AND (p1.name LIKE :searchName or p2.user_name LIKE :searchName)");
            }
            long userId = 0;
            if (model.getUser() != null && model.getUser().getUserId() > 0) {
                sql.append(" AND p1.user_id = :userId");
                userId = model.getUser().getUserId();
            }
            if (model.getScales() == 99) {
                sql.append(" AND p1.account_yes > account");
            }
            String borrowName = "";
            if (StringUtil.isNotBlank(model.getName()) && !"undefined".equals(model.getName()) && model.getName().length() > 0) {
                borrowName = model.getName();
                sql.append(" AND p1.name LIKE :borrowName");
            }
            String userName = "";
            if (StringUtil.isNotBlank(model.getUserName())) {
                userName = model.getUserName();
                sql.append(" AND p2.user_name LIKE :userName");
            }
            Date d = DateUtil.getDate(System.currentTimeMillis() / 1000 + "");
            Date startTime = null;
            Date endTime = null;
            if (model.getTime() == 7) {
                sql.append(" AND p1.add_time >= :startTime");
                sql.append(" AND p1.add_time <= :endTime");
                startTime = DateUtil.rollDay(d, -7);
                endTime = d;
            } else if (model.getTime() > 0 && model.getTime() < 4) {
                sql.append(" AND p1.add_time >= :startTime");
                sql.append(" AND p1.add_time <= :endTime");
                startTime = DateUtil.rollMon(d, -model.getTime());
                endTime = d;
            }
            if (StringUtil.isNotBlank(model.getStartTime())) {
                startTime = DateUtil.valueOf(model.getStartTime() + " 00:00:00");
                sql.append(" AND p1.add_time > :startTime");
            }
            if (StringUtil.isNotBlank(model.getEndTime())) {
                endTime = DateUtil.valueOf(model.getEndTime() + " 23:59:59");
                sql.append(" AND p1.add_time < :endTime");
            }
            int type = 0;
            if (model.getType() != 100 && model.getType() != 0) {
                type = model.getType();
                sql.append(" AND p1.type = :type");
            }
            sql.append(this.searchParam(model));
            PageDataList<Borrow> pageDataList = new PageDataList<Borrow>();
            selectSql.append(sql);
            Query query = em.createNativeQuery(selectSql.toString(), Borrow.class);
            if (userId > 0) {
                query.setParameter("userId", userId);
            }
            if (startTime != null) {
                query.setParameter("startTime", startTime);
            }
            if (endTime != null) {
                query.setParameter("endTime", endTime);
            }
            if (searchName.length() > 0) {
                query.setParameter("borrowName", "%" + searchName.trim() + "%");
            }
            if (borrowName.length() > 0) {
                query.setParameter("borrowName", "%" + borrowName.trim() + "%");
            }
            if (userName.length() > 0) {
                query.setParameter("userName", "%" + userName.trim() + "%");
            }
            if (type > 0) {
                query.setParameter("type", type);
            }
            Page page = new Page(query.getResultList().size(), model.getPage(), model.getRows());
            query.setFirstResult((model.getPage() - 1) * model.getRows());
            query.setMaxResults(model.getRows());
            List<Borrow> list = query.getResultList();
            pageDataList.setList(list);
            pageDataList.setPage(page);
            return pageDataList;
        }
        return null;
    }

    @Override
    public void updateRepaymentAccount(long borrowId, double repayMoney) {
        String sql = "UPDATE Borrow SET repaymentAccount = :repayMoney WHERE id = :id";
        Query query = em.createQuery(sql);
        query.setParameter("repayMoney", repayMoney);
        query.setParameter("id", borrowId);
        int result = query.executeUpdate();
        if (result != 1) {
            throw new BusinessException("标审核，更改borrow表中repaymentAccount与repayment表中repaymentAccount保持一致失败！", 1);
        }
        refresh(super.find(borrowId));
    }

    @Override
    public long ipsTrialCount() {
        String sql = "select count(*) from Borrow where status=11 AND ((LENGTH(guaranteeNo) >0 AND guaranteeRate > 0) OR guaranteeRate = 0)";
        Query query = em.createQuery(sql);
        return (Long) query.getSingleResult();
    }

    @Override
    public int getBorrowCountByDate(String date) {
        String sql = "select count(distinct user_id) from " + Global.DB_PREFIX + "borrow where date_format(add_time,'%Y-%m') = ?";
        Query query = em.createNativeQuery(sql).setParameter(1, date);
        Object obj = query.getSingleResult();
        int count = 0;
        if (obj != null) {
            count = Integer.parseInt((obj.toString()));
        }
        return count;
    }

    @Override
    public double getCountByTypeAndDate(int type, int i, String date) {
        StringBuffer sb = new StringBuffer("select ");
        if (type == 1) {
            sb.append("sum(account)");
        } else {
            sb.append("count(distinct user_id)");
        }
        sb.append(" from " + Global.DB_PREFIX + "borrow where date_format(add_time,'%Y-%m') = ? and ");
        if (i == 0) {
            sb.append("account between 0 and 9999");
        } else if (i == 1) {
            sb.append("account between 10000 and 99999");
        } else if (i == 2) {
            sb.append("account between 100000 and 499999");
        } else if (i == 3) {
            sb.append("account between 500000 and 999999");
        } else {
            sb.append("account >= 1000000");
        }
        Query query = em.createNativeQuery(sb.toString()).setParameter(1, date);
        Object obj = query.getSingleResult();
        double result = 0;
        if (obj != null) {
            result = Double.parseDouble(obj + "");
        }
        return result;
    }

    @Override
    public double getCountByTypeAndBorrowTypeAndDate(int type, int borrowType, String date) {
        StringBuffer sb = new StringBuffer("select ");
        if (type == 1) {
            sb.append("sum(account)");
        } else {
            sb.append("count(id)");
        }
        sb.append("from " + Global.DB_PREFIX + "borrow where category!=2 and date_format(add_time,'%Y-%m') = ? and type=?");
        Query query = em.createNativeQuery(sb.toString()).setParameter(1, date).setParameter(2, borrowType);
        Object obj = query.getSingleResult();
        double result = 0;
        if (obj != null) {
            result = Double.parseDouble(obj + "");
        }
        return result;
    }

    @Override
    public double getDealCountByTypeAndBorrowTypeAndDate(int type, int borrowType, String date) {
        StringBuffer sb = new StringBuffer("select ");
        if (type == 1) {
            sb.append("count(id)");
        } else {
            sb.append("sum(account)");
        }
        sb.append("from " + Global.DB_PREFIX + "borrow where status in (3,6,7,8) and date_format(review_time,'%Y-%m') = ? and type=?");
        Query query = em.createNativeQuery(sb.toString()).setParameter(1, date).setParameter(2, borrowType);
        Object obj = query.getSingleResult();
        double result = 0;
        if (obj != null) {
            result = Double.parseDouble(obj + "");
        }
        return result;
    }

    @Override
    public double getDealByTypeAndBorrowTypeAndDate(int type, int borrowType, String date) {
        StringBuffer sb = new StringBuffer("select ");
        if (type == 3) {
            sb.append("count(id)");
        } else {
            sb.append("sum(account)");
        }
        sb.append("from " + Global.DB_PREFIX + "borrow where status in (3,6,7,8) and date_format(review_time,'%Y-%m') = ? and type=?");
        Query query = em.createNativeQuery(sb.toString()).setParameter(1, date).setParameter(2, borrowType);
        Object obj = query.getSingleResult();
        double result = 0;
        if (obj != null) {
            result = Double.parseDouble(obj + "");
        }
        return result;
    }

    @Override
    public double getTurnoverByTypeAndDate(int type, String date) {
        StringBuffer sb = new StringBuffer("select ");
        if (type == 1) {
            sb.append("sum(account)");
        } else {
            sb.append("count(id)");
        }
        sb.append(" from " + Global.DB_PREFIX + "borrow where status in (3,6,7,8) and category!=2 and date_format(review_time,'");
        if (date.length() == 4) {
            sb.append("%Y");
        } else if (date.length() == 7) {
            sb.append("%Y-%m");
        } else if (date.length() == 10) {
            sb.append("%Y-%m-%d");
        } else {
            sb.append("%Y-%m-%d %H");
        }
        sb.append("') = ?");
        Query query = em.createNativeQuery(sb.toString()).setParameter(1, date);
        Object obj = query.getSingleResult();
        double result = 0;
        if (obj != null) {
            result = Double.parseDouble(obj + "");
        }
        return result;
    }

    @Override
    public double getCumulativeBorrowByDate(String date) {
        StringBuffer sb = new StringBuffer("select count(distinct user_id) from " + Global.DB_PREFIX + "borrow where status in (3,6,7,8) and date_format(review_time,'");
        if (date.length() == 4) {
            sb.append("%Y");
        } else {
            sb.append("%Y-%m");
        }
        sb.append("') = ?");
        Query query = em.createNativeQuery(sb.toString()).setParameter(1, date);
        Object obj = query.getSingleResult();
        double result = 0;
        if (obj != null) {
            result = Double.parseDouble(obj + "");
        }
        return result;
    }


    @SuppressWarnings("unchecked")
    @Override
    public List<String> getBorrowCardIdsByDate(String date) {
        String sql = "select distinct(card_id) from " + Global.DB_PREFIX + "user u, " + Global.DB_PREFIX + "borrow b where u.user_id=b.user_id and date_format(b.add_time,'%Y-%m')=?";
        Query query = em.createNativeQuery(sql).setParameter(1, date);
        return query.getResultList();
    }

    @SuppressWarnings("unchecked")
    @Override
    public Borrow getLastBorrow() {
        String sql = "SELECT p1.* FROM " + Global.DB_PREFIX + "borrow p1," + Global.DB_PREFIX + "user p2," + Global.DB_PREFIX + "verify_log p3 WHERE p1.user_id = p2.user_id AND "
                + "(p1.id=p3.fid AND p3.type='borrow' AND p3.result = 1 AND p3.verify_type = 1 "
                + "AND p1.status = 1  AND p1.account_yes <= p1.account OR p1.type =110 AND p1.status = 1 AND p1.scales!=100 OR p1.status IN (3,6,7,8))"
                + " GROUP BY p1.id ORDER BY p1.status ASC , p1.id DESC limit 1";
        Query query = em.createNativeQuery(sql, Borrow.class);
        List<Borrow> list = query.getResultList();
        Borrow borrow = new Borrow();
        if (list != null && list.size() > 0) {
            borrow = list.get(0);
        }
        return borrow;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Borrow> getBorrowByUserId(long userId) {
        String sql = "SELECT t.* FROM " + Global.DB_PREFIX + "borrow t WHERE t.user_id=:user_id";
        Query query = em.createNativeQuery(sql, Borrow.class);
        query.setParameter("user_id", userId);
        List<Borrow> list = query.getResultList();
        return list;
    }

    /**
     * 首页推荐标显示
     *
     * @param model 标model
     * @return
     */
    @Override
    public PageDataList<Borrow> getLastBorrowForIndexExp(BorrowModel model) {
        StringBuilder selectSql = new StringBuilder(" SELECT * FROM " + Global.DB_PREFIX + "borrow WHERE category = 2 AND scales = 100 AND status NOT IN (- 1, 0) ORDER BY put_start_time DESC ");
        if (model != null) {
            PageDataList<Borrow> pageDataList = new PageDataList<Borrow>();
            Query query = em.createNativeQuery(selectSql.toString(), Borrow.class);

            // 分页查询。以上selectSql不用加[limit 0,3],Page会自动设置
            Page page = new Page(query.getResultList().size(), model.getPage(), model.getRows());
            query.setFirstResult((model.getPage() - 1) * model.getRows());
            query.setMaxResults(model.getRows());
            List<Borrow> list = query.getResultList();
            pageDataList.setList(list);
            pageDataList.setPage(page);
            return pageDataList;
        }
        return null;
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<Borrow> getBorrowByStatusAndIds(int status,List<Long> idList) {
        StringBuffer sql = new StringBuffer("SELECT b.* FROM " + Global.DB_PREFIX + "borrow b WHERE b.status != ? AND b.id IN (");
        HashSet<Long> ids = new HashSet<Long>(idList);// 去重
        sql.append(StringUtils.repeat(" ? ", ",", ids.size()));
        sql.append(") ");
        Query query = em.createNativeQuery(sql.toString(), Borrow.class);
        query.setParameter(1, status);
        Object[] queryParams= ids.toArray();
        if (queryParams != null && queryParams.length > 0) {
            for (int i = 0; i < queryParams.length; i++) {
                query.setParameter(i + 2, queryParams[i]);
            }
        }
        List<Borrow> list = query.getResultList();
        return list;
    }
}
